﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using KVB.Common;

namespace Customer_Profile.eCard.DBDao
{
    public class netBankDao
    {
        public static DataSet getNetBankUploadList()
        {
            string strSQL = "SELECT * FROM dbo.netbank_batch where 1=1 ";
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                ds = DB.ExecuteDataset(conn, CommandType.Text, strSQL);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("CpartyDao.getNetBankUploadList", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getNetbankDetailbyDate(string strDate,string bankname)
        {   
            //and (match_balance IS NULL OR match_balance !=0 ) 
            string strSQL = "SELECT seq_no,accounting_key,business_date,cparty_bankname,cparty_accountname,cparty_accountno,crebit_amount,match_balance, remark,status FROM dbo.netbank_detail WHERE  crebit_amount!=0 and convert(date,business_date)='" + strDate + "' and bank_name='"+ bankname +"'";
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                ds = DB.ExecuteDataset(conn, CommandType.Text, strSQL);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("CpartyDao.getNetbankDetailbyDate", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getMatchTransfer(int deal_no)
        {
            string strSQL = "SELECT transfer_id, deal_no,card_no,transfer_type,account_name,bank_name,account_no,transfer_amount,to_account,transfer_date,remark,netbank_seqno FROM dbo.bank_transfer WHERE deal_no =" + deal_no;
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                ds = DB.ExecuteDataset(conn, CommandType.Text, strSQL);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("CpartyDao.getMatchTransfer", e.Message);
                throw e;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getDealAllInfo(int deal_no)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@deal_no", SqlDbType.Int)                                             
                                           };
                parameters[0].Value = deal_no;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_deals_matchInfo", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("netBankDao.getDealAllInfo", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getnetbankInfo_byseqno(string netbank_seqno)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@seq_no", SqlDbType.Int)                                             
                                           };
                parameters[0].Value = netbank_seqno;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Match_netbankInfo", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("netBankDao.getnetbankInfo_byseqno", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getnetbankInfo_byBatchid(string netbank_batch_no)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@batch_no", SqlDbType.Int)                                             
                                           };
                parameters[0].Value =int.Parse(netbank_batch_no);
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_netBank_infoList", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("netBankDao.getnetbankInfo_byBatchid", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getnetbankInfo_byBankname(string bank_name,string start_date,string end_date)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@bank_name", SqlDbType.VarChar,250),
                                             new SqlParameter("@start_date", SqlDbType.VarChar,250),
                                             new SqlParameter("@end_date", SqlDbType.VarChar,250)
                                           };
                parameters[0].Value = bank_name;
                parameters[1].Value = start_date;
                parameters[2].Value = end_date;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_netBank_byBankName", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("netBankDao.getnetbankInfo_byBankname", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet deleteNetbankbySeqNo(int seq_no,out string status,out string message)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@seq_no", SqlDbType.Int),
                                             new SqlParameter("@status", SqlDbType.VarChar,250),
                                             new SqlParameter("@message", SqlDbType.VarChar,500)
                                           };
                parameters[0].Value = seq_no;
                parameters[1].Direction = ParameterDirection.Output;
                parameters[2].Direction = ParameterDirection.Output; ;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_netBank_DeletebySeqNO", parameters);
                status = parameters[1].Value.ToString();
                message = parameters[2].Value.ToString(); 
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("netBankDao.deleteNetbankbySeqNo", ex.ToString());
                status = "N";
                message = ex.ToString();
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getDealAllInfo_bySettleID(int settle_id)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@settle_id", SqlDbType.Int)                                             
                                           };
                parameters[0].Value = settle_id;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_deals_SettleInfo", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("netBankDao.getDealAllInfo", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet CalcValueDate(int deal_no)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@deal_no", SqlDbType.Int)                                             
                                           };
                parameters[0].Value = deal_no;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_deals_calcValueDate", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("netBankDao.getDealAllInfo", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static void Review(int deal_no,string strUserid, string value_date,string due_date,string remark, out string review_status, out string strMessage)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@deal_no", SqlDbType.Int),
                                             new SqlParameter("@review_userid", SqlDbType.VarChar,50),
                                             new SqlParameter("@value_date", SqlDbType.VarChar,50),
                                             new SqlParameter("@due_date", SqlDbType.VarChar,50),
                                             new SqlParameter("@remark", SqlDbType.VarChar,250),
                                             new SqlParameter("@review_status", SqlDbType.VarChar,50),
                                             new SqlParameter("@message", SqlDbType.VarChar,250)
                                           };
                parameters[0].Value = deal_no;
                parameters[1].Value = strUserid;
                parameters[2].Value = value_date;
                parameters[3].Value = due_date;
                parameters[4].Value = remark;
                parameters[5].Direction = ParameterDirection.Output;
                parameters[6].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_deals_Review", parameters);

                review_status = parameters[5].Value.ToString();
                strMessage = parameters[6].Value.ToString();
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("netBankDao.Review", ex.Message);
                review_status = "";
                strMessage = ex.ToString();
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void Review_Back(int deal_no,string user_id, string back_reason, out string review_status, out string strMessage)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@deal_no", SqlDbType.Int),            
                                             new SqlParameter("@review_userid", SqlDbType.VarChar,50),
                                             new SqlParameter("@back_reason", SqlDbType.VarChar,250),
                                             new SqlParameter("@review_status", SqlDbType.VarChar,50),
                                             new SqlParameter("@message", SqlDbType.VarChar,250)
                                           };
                parameters[0].Value = deal_no;
                parameters[1].Value = user_id;
                parameters[2].Value = back_reason;
                parameters[3].Direction = ParameterDirection.Output;
                parameters[4].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_deals_ReviewBack", parameters);

                review_status = parameters[3].Value.ToString();
                strMessage = parameters[4].Value.ToString();
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("netBankDao.Review", ex.Message);
                review_status = "";
                strMessage = ex.ToString();
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static bool MatchAction(string user_id, string transfer_id,string accounting_key,out string status,out string message)
        {
            bool bok = false;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@user_id ", SqlDbType.VarChar,250),
                                             new SqlParameter("@transfer_id ", SqlDbType.VarChar,250),
                                             new SqlParameter("@accounting_key ", SqlDbType.VarChar,250),
                                             new SqlParameter("@status", SqlDbType.VarChar,250),
                                             new SqlParameter("@message", SqlDbType.VarChar,250)
                                           };
                parameters[0].Value = user_id;
                parameters[1].Value = transfer_id;
                parameters[2].Value = accounting_key;
                parameters[3].Direction = ParameterDirection.Output;
                parameters[4].Direction = ParameterDirection.Output;
                
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_BankTransfer_Match", parameters);
                status =parameters[3].Value.ToString();
                message = parameters[4].Value.ToString();
                if (status.Equals("Y"))
                {
                    bok = true;
                }
                else
                {
                    bok = false;
                }
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("netBankDao.MatchAction", e.Message);
                bok = false;
                status ="N";
                message = e.ToString(); ;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            
            return bok;
        }

        public static bool UnMatchAction(string user_id, string transfer_id, string accounting_key, out string status, out string message)
        {
            bool bok = false;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@user_id ", SqlDbType.VarChar,250),
                                             new SqlParameter("@transfer_id ", SqlDbType.VarChar,250),
                                             new SqlParameter("@accounting_key ", SqlDbType.VarChar,250),
                                             new SqlParameter("@status", SqlDbType.VarChar,250),
                                             new SqlParameter("@message", SqlDbType.VarChar,250)
                                           };
                parameters[0].Value = user_id;
                parameters[1].Value = transfer_id;
                parameters[2].Value = accounting_key;
                parameters[3].Direction = ParameterDirection.Output;
                parameters[4].Direction = ParameterDirection.Output;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_BankTransfer_UnMatch", parameters);
                status = parameters[3].Value.ToString();
                message = parameters[4].Value.ToString();
                if (status.Equals("Y"))
                {
                    bok = true;
                }
                else
                {
                    bok = false;
                }
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("netBankDao.UnMatchAction", e.Message);
                bok = false;
                status = "N";
                message = e.ToString(); ;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }

            return bok;
        }

        public static DataSet getMatchedList(string start_date,string end_date)
        {
            
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@start_date", SqlDbType.VarChar,250),
                                             new SqlParameter("@end_date", SqlDbType.VarChar,250)                                             
                                           };
                parameters[0].Value = start_date;
                parameters[1].Value = end_date;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Matched_list", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("CpartyDao.getMatchTransfer", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }
    }
}